#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
通知表字段迁移脚本
添加 notification_scope 和 expire_time 字段
"""

import sys
import os
from sqlalchemy import text

# 添加项目根目录到Python路径
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

from database.connection import engine

def migrate_notification_table():
    """
    为 user_notifications 表添加新字段
    """
    print("开始迁移通知表...")
    
    try:
        with engine.connect() as conn:
            # 检查字段是否已存在
            check_scope_sql = """
            SELECT COUNT(*) as count 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = 'aigd100' 
            AND TABLE_NAME = 'user_notifications' 
            AND COLUMN_NAME = 'notification_scope'
            """
            
            check_expire_sql = """
            SELECT COUNT(*) as count 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = 'aigd100' 
            AND TABLE_NAME = 'user_notifications' 
            AND COLUMN_NAME = 'expire_time'
            """
            
            scope_exists = conn.execute(text(check_scope_sql)).fetchone()[0] > 0
            expire_exists = conn.execute(text(check_expire_sql)).fetchone()[0] > 0
            
            # 添加 notification_scope 字段
            if not scope_exists:
                add_scope_sql = """
                ALTER TABLE user_notifications 
                ADD COLUMN notification_scope INT NOT NULL DEFAULT 1 
                COMMENT '通知范围：1-个人通知 2-公共通知'
                """
                conn.execute(text(add_scope_sql))
                print("✅ 添加 notification_scope 字段成功")
            else:
                print("ℹ️  notification_scope 字段已存在")
            
            # 添加 expire_time 字段
            if not expire_exists:
                add_expire_sql = """
                ALTER TABLE user_notifications 
                ADD COLUMN expire_time DATETIME DEFAULT NULL 
                COMMENT '通知过期时间'
                """
                conn.execute(text(add_expire_sql))
                print("✅ 添加 expire_time 字段成功")
            else:
                print("ℹ️  expire_time 字段已存在")
            
            # 添加索引
            if not scope_exists:
                add_scope_index_sql = """
                CREATE INDEX idx_notification_scope ON user_notifications(notification_scope)
                """
                try:
                    conn.execute(text(add_scope_index_sql))
                    print("✅ 添加 notification_scope 索引成功")
                except Exception as e:
                    if "Duplicate key name" in str(e):
                        print("ℹ️  notification_scope 索引已存在")
                    else:
                        print(f"⚠️  添加 notification_scope 索引失败: {str(e)}")
            
            if not expire_exists:
                add_expire_index_sql = """
                CREATE INDEX idx_expire_time ON user_notifications(expire_time)
                """
                try:
                    conn.execute(text(add_expire_index_sql))
                    print("✅ 添加 expire_time 索引成功")
                except Exception as e:
                    if "Duplicate key name" in str(e):
                        print("ℹ️  expire_time 索引已存在")
                    else:
                        print(f"⚠️  添加 expire_time 索引失败: {str(e)}")
            
            # SQLAlchemy连接会自动提交DDL语句
            
        print("✅ 通知表迁移完成")
        return True
        
    except Exception as e:
        print(f"❌ 迁移失败: {str(e)}")
        return False

def verify_migration():
    """
    验证迁移是否成功
    """
    print("\n验证迁移结果...")
    
    try:
        with engine.connect() as conn:
            # 查询表结构
            sql = """
            SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = 'aigd100' 
            AND TABLE_NAME = 'user_notifications'
            AND COLUMN_NAME IN ('notification_scope', 'expire_time')
            ORDER BY COLUMN_NAME
            """
            
            result = conn.execute(text(sql)).fetchall()
            
            if len(result) == 2:
                print("✅ 新字段验证成功")
                print("字段信息:")
                for row in result:
                    print(f"  - {row[0]}: {row[1]} (默认值: {row[3]}, 注释: {row[4]})")
                return True
            else:
                print(f"❌ 字段验证失败，找到 {len(result)} 个字段，期望 2 个")
                return False
                
    except Exception as e:
        print(f"❌ 验证失败: {str(e)}")
        return False

if __name__ == "__main__":
    print("=" * 60)
    print("通知表字段迁移")
    print("=" * 60)
    
    # 执行迁移
    migration_result = migrate_notification_table()
    
    # 验证迁移
    if migration_result:
        verify_result = verify_migration()
    else:
        verify_result = False
    
    print("\n=" * 60)
    print("迁移结果汇总:")
    print(f"字段迁移: {'✅ 成功' if migration_result else '❌ 失败'}")
    print(f"迁移验证: {'✅ 成功' if verify_result else '❌ 失败'}")
    print("=" * 60)
    
    if migration_result and verify_result:
        print("\n🎉 通知表迁移完成！现在可以使用新的通知功能了。")
    else:
        print("\n❌ 迁移过程中出现问题，请检查错误信息。")